/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import model.Rapport;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.mapping.Map;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import persistence.SessionManager;
import util.KwartaalHelper;

/**
 *
 * @author Johan
 */
public class RapportController extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        NumberFormat nf = new DecimalFormat("#0.0");

        //hibernate session
        Session session = null;
        session = SessionManager.openSession();
        Transaction tx = session.beginTransaction();

        int rapportType;
        try {
            rapportType = Integer.parseInt(request.getParameter("radios"));
        } catch (Exception e) {
            rapportType = 0;
        }

        String sqlquery;
        String kwartaal = request.getParameter("kwartaal");
        String jaar = request.getParameter("jaar");
        String provincie = request.getParameter("provincie");

        switch (rapportType) {
            case 1: //gebruikersrapportage
                BigDecimal avgAgeHelper;
                BigInteger totalMembersHelper;
                BigInteger newMembersHelper;
                Double avgAge;
                Integer totalMembers = 0;
                Integer newMembers = 0;

                //gemiddelde leeftijd
                sqlquery = "SELECT AVG(YEAR(L.LID_GEBOORTEDATUM)) FROM lid L WHERE YEAR(L.LID_REGISTERED) <= " + jaar;
                if (kwartaal != "") {
                    sqlquery += " AND L.LID_REGISTERED <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                }
                if (provincie != "") {
                    sqlquery += " AND L.LID_PROVINCIE = '" + provincie + "' ";
                }
                System.out.println("sqlquery is: " + sqlquery);
                try {
                    avgAgeHelper = (BigDecimal) session.createSQLQuery(sqlquery).uniqueResult();
                    avgAge = 2012 - avgAgeHelper.doubleValue();
                } catch (Exception e) {
                    avgAge = 0.0;
                }

                //aantal gebruikers
                sqlquery = "SELECT count(*) FROM lid L WHERE YEAR(L.LID_REGISTERED) <= " + jaar;
                if (kwartaal != "") {
                    sqlquery += " AND L.LID_REGISTERED <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                }
                if (provincie != "") {
                    sqlquery += " AND L.LID_PROVINCIE = '" + provincie + "' ";
                }

                try {
                    totalMembersHelper = (BigInteger) session.createSQLQuery(sqlquery).uniqueResult();
                    totalMembers = totalMembersHelper.intValue();
                } catch (Exception e) {
                    totalMembers = 0;
                }

                //nieuwe gebruikers
                sqlquery = "SELECT count(*) FROM lid L WHERE YEAR(L.LID_REGISTERED) = " + jaar;
                if (kwartaal != "") {
                    sqlquery += " AND L.LID_REGISTERED >= '" + KwartaalHelper.getFirstDate(jaar, kwartaal) + "'";
                    sqlquery += " AND L.LID_REGISTERED <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                }
                if (provincie != "") {
                    sqlquery += " AND L.LID_PROVINCIE = '" + provincie + "' ";
                }

                try {
                    newMembersHelper = (BigInteger) session.createSQLQuery(sqlquery).uniqueResult();
                    newMembers = newMembersHelper.intValue();
                } catch (Exception e) {
                    newMembers = 0;
                }

                request.setAttribute("gemiddeldeleeftijd", nf.format(avgAge));
                request.setAttribute("aantalleden", totalMembers);
                request.setAttribute("nieuweleden", newMembers);

                break;
            case 2: //advertentierapportage
                BigInteger totalAdvHelper;
                BigInteger newAdvHelper;
                Integer totalAdv = 0;
                Integer newAdv = 0;

                //totaal aantal advertenties
                sqlquery = "SELECT count(*) FROM Advertentie WHERE YEAR(ADV_DATUM) <= " + jaar;
                if (kwartaal != "") {
                    sqlquery += " AND ADV_DATUM <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                }
                if (provincie != "") {
                    sqlquery += " AND ADV_PROVINCIE = '" + provincie + "' ";
                }

                try {
                    totalAdvHelper = (BigInteger) session.createSQLQuery(sqlquery).uniqueResult();
                    totalAdv = totalAdvHelper.intValue();
                } catch (Exception e) {
                    totalAdv = 0;
                }

                //nieuwe advertenties
                sqlquery = "SELECT count(*) FROM advertentie WHERE YEAR(ADV_DATUM) = " + jaar;
                if (kwartaal != "") {
                    sqlquery += " AND ADV_DATUM >= '" + KwartaalHelper.getFirstDate(jaar, kwartaal) + "'";
                    sqlquery += " AND ADV_DATUM <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                }
                if (provincie != "") {
                    sqlquery += " AND ADV_PROVINCIE = '" + provincie + "' ";
                }

                try {
                    newAdvHelper = (BigInteger) session.createSQLQuery(sqlquery).uniqueResult();
                    newAdv = newAdvHelper.intValue();
                } catch (Exception e) {
                    newAdv = 0;
                }

                //lijst met advertenties
                sqlquery = "SELECT A.ADV_DATUM, L.LID_DISPLAYNAAM, A.ADV_NR_OF_VIEWS, A.ADV_DELETED FROM Advertentie A INNER JOIN Lid L ON A.LID_ID = L.LID_ID WHERE YEAR(A.ADV_DATUM) = " + jaar;
                if (kwartaal != "") {
                    sqlquery += " AND A.ADV_DATUM >= '" + KwartaalHelper.getFirstDate(jaar, kwartaal) + "' AND A.ADV_DATUM <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                }
                if (provincie != "") {
                    sqlquery += " AND A.ADV_PROVINCIE = '" + provincie + "'";
                }
                sqlquery += " ORDER BY L.LID_DISPLAYNAAM";

                List<Object[]> advertentieLijst = new ArrayList();
                advertentieLijst = session.createSQLQuery(sqlquery).list();

                request.setAttribute("totaaladvertenties", totalAdv);
                request.setAttribute("nieuweadvertenties", newAdv);
                request.setAttribute("advertentielijst", advertentieLijst);
                request.setAttribute("advertentielijstaantal", advertentieLijst.size());

                break;
            case 3: //rittenrapportage
                String sqlkwartaal;
                if (kwartaal != "") {
                    sqlkwartaal = " AND rit_datum >= '" + KwartaalHelper.getFirstDate(jaar, kwartaal) + "' AND rit_datum <= '" + KwartaalHelper.getLastDate(jaar, kwartaal) + "'";
                } else {
                    sqlkwartaal = "";
                }
                if (provincie != "") {
                    sqlquery = "SELECT rit_van_plaats, count(*) FROM rit WHERE rit_deleted != 1  AND YEAR(rit_datum) = " + jaar + " AND rit_van_provincie = '" + provincie + "'";
                    sqlquery += sqlkwartaal;
                    sqlquery += " GROUP BY rit_van_plaats";
                } else {
                    sqlquery = "SELECT rit_van_provincie, count(*) FROM rit WHERE rit_deleted != 1 AND YEAR(rit_datum) = " + jaar;
                    sqlquery += sqlkwartaal;
                    sqlquery += " GROUP BY rit_van_provincie";
                }

                List<Object[]> lokatieLijst = new ArrayList();
                lokatieLijst = session.createSQLQuery(sqlquery).list();

                request.setAttribute("lokatielijst", lokatieLijst);
                request.setAttribute("lokatielijstsize", lokatieLijst.size());
                break;
        }

        //geef geselecteerde rapport (en gegevens) weer mee aan de request
        request.setAttribute("jaar", jaar);
        request.setAttribute("kwartaal", kwartaal);
        request.setAttribute("provincie", provincie);
        request.setAttribute("rapporttype", rapportType);

        //ga naar rapportage.jsp
        String address = "/rapportage.jsp";
        RequestDispatcher dispatcher = request.getRequestDispatcher(address);
        dispatcher.forward(request, response);

        //close de hibernate sessie
        session.close();
    }
}
